Oracle 日常基本命令

您所在的位置：网站首页 › oracle 启动命令 › Oracle 日常基本命令

Oracle 日常基本命令

#Oracle 日常基本命令| 来源: 网络整理| 查看: 265

文章目录 Oracle 日常基本命令用户与权限创建用户给用户修改密码修改自己的密码删除用户给用户赋权限收回用户权限系统权限：是数据库管理相关的权限：connnect 角色：resource 角色：dba 角色：表空间创建表空间必须使用 system 用户创建创建用户指定默认表空间修改用户默认表空间：查看表空间：查看用户默认表空间：表结构的操作创建表：修改表添加列修改列删除列修改表的名称修改列名查看表结构约束非空（NOT NULL）添加非空删除非空唯一（UNIQUE）添加修改唯一删除唯一主键（PRIMARY KEY）添加修改主键删除主键外键（FOREIGN KEY）添加修改主键删除主键条件（CHECK）添加修改条件删除条件显示表约束信息数据操作添加数据插入全部数据从表中导入数据修改数据删除数据基本查询数据基本语法：使用列的别名如何处理 null 值如何连接字符串(||)如何使用 like 操作符在 where 条件中使用 in在查询语句中不能使用=或者!=null判断是否为空使用逻辑操作符号使用 order by 子句Oracle分组查询Max(),min()最大最小Avg()求平均count(*)求总数 group by 和 having 子句Oralce函数Ascii()chr()concat()initcap()instr(C1,C2,I,J)length()lower()upper()rpad 和 lpad(粘贴字符)ltrim 和 rtrimsubstr(string,start,count)replace('string','s1','s2')trim('s' from 'string') 数学函数ceil（向上取整）floor（向下取整）trunc(精度截取)round 和 trunkabsacosasinatancosmod(n1,n2) 日期函数add_months(掌握)last_daymonths_between(date2,date1)next_day(date,'day')Sysdate转换类型函数to_char(date,'format')to_date(string,'format')to_number 系统函数表之间的连接自连接内连接(inner join - on)左外连接 (left join - on)右外连接 (right join - on)完全外连 (full outer join - on) 分页查询oracle视图创建视图基本语法：创建或修改视图基本语法：删除视图基本语法：

Oracle 日常基本命令用户与权限创建用户 create user 用户名 identified by 密码; 给用户修改密码 alter user 用户名 identified by 新密码; 修改自己的密码 password 用户名; 删除用户 drop user 用户名 [cascade]可选参数 cascade

在删除用户时，注意：如果要删除的用户，已经创建了表，那么就需要在删除的时候带一个参数 cascade; Cascade 有级联的作用

给用户赋权限 grant 权限/角色 to 用户名; 收回用户权限 revoke 权限/角色 from 用户名; 系统权限：是数据库管理相关的权限： create session(登录权限) create table(创建表权限) create index(创建索引权限) create view(创建视图权限) create sequence(创建序列权限) create trriger(创建触发器权限) connnect 角色：

是授予用户的最基本的权利，能够连接到 oralce 数据库中，并在对其他用户的表有访问权限时，做 SELECT、UPDATE、INSERTT 等操作。

Create session--建立会话 Alter session--修改会话 Create view--建立视图 Create sequence--建立序列等权限 resource 角色：

具有创建表、序列、视图等权限。

Create session--建立会话 Alter session--修改会话 Create view--建立视图 Create sequence--建立序列等权限 dba 角色：

是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限。

表空间创建表空间必须使用 system 用户创建 create tablespace hpu56（表空间名） datafile 'E:\datebase\hpu6.DBF' //指定指向的数据文件路径 size 5m //初始化表空间大小为 5m autoextend on next 2m //自动扩展，每次扩展 2m maxsize unlimited; // unlimited最大扩展量没有限制，100M最大扩展到100M 创建用户指定默认表空间 create user 用户名 identified by 密码 default tablespace 表空间名; 修改用户默认表空间： alter user 用户名 default tablespace 表空间名; 查看表空间： alter user 用户名 default tablespace 表空间名; 查看用户默认表空间： select username,default_tablespace from dba_users where username='SCOTT';

注意：这里的用户名 SCOTT 必须是大写

表结构的操作创建表： create table 表的名称（字段名称 1 数据类型, 字段名称 2 数据类型）; create table 表的名称 as select 字段名称 1, 字段名称 2... from表的名称 ; 修改表 alter table 语句添加、修改或删除列的语法添加列 alter table 表的名称 add(字段名称1 数据类型, 字段名称2 数据类型...); alter table 表的名称 add 字段名称数据类型; 修改列 alter table 表名 modify(字段名称1 数据类型, 字段名称2 数据类型...); alter table 表名 modify字段名称数据类型; 删除列 alter table 表名 drop(字段名称1 , 字段名称2,...); alter table 表名 drop 字段名称; 修改表的名称 rename 表名 to 新表名; 修改列名 alter table 表名 rename column 旧列名 to 新列名; 查看表结构 desc 表名; 约束

约束分为：非空（NOT NULL）约束、唯一（UNIQUE）约束、主键（PRIMARY KEY）约束、外键（FOREIGN KEY）约束、条件（CHECK）约束，约束存在表user_constraints中。

非空（NOT NULL）添加非空 create table 表名称 (字段名称1 数据类型,字段名称2 数据类型 not null); alter table 表名 modify 字段名 not null; 删除非空 alter table 表名 modify 字段名 null; 唯一（UNIQUE）添加修改唯一 create table 表名称 (字段名称1 数据类型,字段名称2 数据类型 unique); alter table 表名 add constraint 约束名 unique (字段名称1,字段名称2); 删除唯一 alter table 表名 drop constratint 约束名称;

注意：oracle 中 unique 可以为 null，而且允许多行为 null

主键（PRIMARY KEY）添加修改主键 create table 表名称 (字段名称1 数据类型,字段名称2 数据类型 primary key); create table 表名称 (字段名称1 数据类型,字段名称2 数据类型 , constraint 约束名primary key (字段名称1,字段名称2)); alter table 表名 add constraint 约束名 primary key (字段名称1,字段名称2); 删除主键 alter table 表名 drop primary key cascade;

这是因为如果在两张表存在主从关系，那么在删除主要的主键约束时，必需带上 cascade 选项

注意：每个表只能有且有一个主键约束。

特别说明 primary key 与 unique 的区别：

1、一张表可以有多个 unique(唯一)约束；

2、一张表只能有一个主键；

3、设置为主键的列不能有 null 值；

外键（FOREIGN KEY）

外键（FOREIGN KEY）约束：用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性.能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库；

用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必需具有主键约束或是 unique 约束，当定义外键约束后，要求外键列数据必需在主表的主键列存在或是为 null

添加修改主键 create table 表名称 (字段名称1 数据类型,字段名称2 数据类型 references 主表名称(字段名) ); create table 表名称 (字段名称1 数据类型,字段名称2 数据类型 , constraint 约束名 foreign key (字段名称) references主表名称(字段名))); alter table 表名 add constraint 约束名 foreign key(字段名称) references 主表名称(字段名); 删除主键 alter table 表名 drop constratint 约束名称;

特别说明：froeign key 外键的细节

1、外键指向主键列；

2、外键可以指向 unique 列；

3、建表时先建主表，再建从表；删除表先删从表，再删主表。

4、外键列属性值要与主键或 unique 列属性值的类型保持一致

5、外键列的值，必需在主键列中存在。但外键列的值允许为 null

条件（CHECK）添加修改条件 create table 表名称 (字段名称1 数据类型,字段名称2 数据类型 check (字段名称2 in (值1,值2...))); alter table 表名 add constraint 约束名 check (字段名称 in (值1,值2...)); 删除条件 alter table 表名 drop constratint 约束名称;

约束命名规则：

非空约束： NN_表名_列名

唯一约束： UK_表名_列名

主键约束： PK_表名

外键约束： FK_表名_列名

条件约束： CK_表名_列名

显示表约束信息 select * from user_constraints where table_name = '表名'; 数据操作添加数据 insert into 表名[(列名 [,列名 2...])] values(值 [,值 2...]); 插入全部数据 insert into 表名 values(值 [,值 2...]); 从表中导入数据 insert into 表名 (select 值1,值2,值3...from 表名2 ); 修改数据 update 表名 set 列名=表达式 [,列名 2=表达式 2,...][where 条件]; 删除数据 delete from 表名 [where 条件表达式]; 删除的几种方法比较： delete from 表名; 删除所有记录，表结构还在，写日志，可以恢复的，速度慢 drop table 表名; 删除表的结构和数据 truncate table 表名; 删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。基本查询数据基本语法： select [是否剔除重复数据] *|{字段名(列名),字段名 2(列名 2),字段名 3(列名 3)..} from 表名 [where {条件}];

注意事项：

1、select 指定查询哪些列的数据；

2、column 指定列名；

3、*代表查询所有列；

4、from 指定查询哪张表；

5、distinct 可选，指显示结果时，是否剔除重复数据；

6、where 条件。

使用列的别名 select ename as "姓名",sal*13+nvl(comm,0)*13 as "年收入" from emp;

特别注意：

oracle 在使用别名时，可以用双引号或不使用或使用 as 来表明别名。但不能使用单引号。

如何处理 null 值 nvl 函数：oracle 提供的函数，是用于处理 null 值使用的。 select ename,sal*13+nvl(comm,0)*13 from emp; nvl(值 1,值 2) 解释：nvl 值 1 为 null 时则取值 2，值 1 不为 null 时则取值 1 原值如何连接字符串(||)

在查询的时候，希望把多列内容做为一列内容返回可以使用||连接符。

select ename ||'年收入'||(sal*13+nvl(comm,0)*13) "雇员的年收入" from emp;

取范围内的值(between)

select * from emp where sal between 2000 and 2500;

between 是指定区间内取值，如：between 2000 and 2500，取 2000 至 2500 内的值，同时包含

2000 和 2500

如何使用 like 操作符

%:表示任意 0 到多个字符

_:表示任意单个字符

1）如何显示首字符为 S 的员工姓名和工资

select ename,sal from emp where ename like 'S%';

2）如何显示第三个字符为大写 O 的所有员工的姓名和工资

select ename,sal from emp where ename link 'O%'; 在 where 条件中使用 in select * from emp where empno in(123,345,800);

注意：in 只能放1000个值

使用 is null 的操作符

在查询语句中不能使用=或者!=null判断是否为空 select * from emp where mgr is null; select * from emp where mgr is not null; 使用逻辑操作符号

查询工资高于 500 或是岗位为 manager 的雇员，同时还要满足他们的姓名首写字母为大写的 J

select * from emp where (sal>500 or job='MANAGER') and (ename like 'J%'); 使用 order by 子句

如何按照工资的从低到高的顺序显示雇员的信息

select * from emp order by sal asc;

注意：asc 写或不写都是升序排序即从小到大排序，desc 则是降序排序从大到小排序。

使用列的别名排序

select ename,sal*12 "年薪" from emp order by "年薪" asc;

别名需要使用“”号圈中。

Oracle分组查询

在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据；要用到分组函数 max,min,avg,sum,count

Max(),min()最大最小

如何显示所有员工中最高工资和最低工资

select max(sal) "最高工资",min(sal) "最低工资" from emp;

请查询最高年工资

select max(sal*13+nvl(comm,0)*13) "最高年工资",min(sal*13+nvl(comm,0)*13) "最低年工资" from emp; Avg()求平均

显示所有员工的平均工资和工资总和

select avg(sal) "平均工资",sum(sal) "工资总和" from emp;

特别注意：

avg(sal)不会把 sal 为 null 的行进行统计，因此我们要注意，如果为空值也考虑，则可以

SQL>selec sum(sal)/count(*) from emp;

count(*)求总数

计算共有多少员工

select count(*) "共有员工" from emp; group by 和 having 子句

group by：用于对查询的结果分组统计；

having 子句：用于限制(过滤)分组显示结果

1）如何显示每个部门的平均工资和最高工资

select avg(sal) "平均工资",max(sal) "最高工资",deptno "部门编号" from emp group by deptno;

2）显示每个部门的每种岗位的平均工资和最低工资

select avg(sal) "平均工资",min(sal) "最低工资",job "职位",deptno "部门编号" from emp group by deptno,job order by deptno;

对数据分组的总结：

1、分组函数(avg…)只能出现在选择列表、having、order by 子句中；

2、如果在 select 语句中同时包含有 group by/having/order by 那么他们的顺序是 group by/having/order

by；

3、在选择列中如果有列、表达式和分组函数，那么这些列和表达式必需有一个出现在 group by 子句中，否则会出错。

Oralce函数 Ascii()

返回与指定的字符对应的十进制数；

select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; chr()

给出整数，返回对应的字符；

select chr(54740) zhao,chr(65) chr65 from dual; concat()

连接两个字符串,与||作用相同；

select concat('hello','world') from dual; initcap()

返回字符串并将字符串的第一个字母变成大写；

select initcap('smith') upp from dual; instr(C1,C2,I,J)

在一个字符串中搜索指定的字符，返回发现指定的字符的位置；

C1 被搜索的字符串；

C2 希望搜索的字符串；

I 搜索的开始位置，默认为 1；

J 第 j 次出现的位置，默认为 1；

select instr('oracle training','ra',1,2) instring from dual; length()

返回字符串的长度；

select ename, length(ename) , job , length(job), sal, length(to_char(sal)) from emp where ename='SMITH'; lower()

返回字符串，并将所有的字符小写；

select lower('AaBbCcDd') "lower-AaBbCcDd" from dual; upper()

返回字符串，并将所有的字符大写；

select upper('AaBbCcDd') "upper-AaBbCcDd" from dual; rpad 和 lpad(粘贴字符)

rpad 在列的右边粘贴字符 rpad('显示内容’或字段,显示长度,‘填充占位符’)

lpad 在列的左边粘贴字符 lpad('显示内容’或字段,显示长度,‘填充占位符’)

select lpad(rpad('htf',10,'*'),17,'*') from dual; ltrim 和 rtrim

ltrim 删除左边出现的字符串 ltrim('原内容’或字段,‘要删除的字符串’)

rtrim 删除右边出现的字符串 rtrim('原内容’或字段,‘要删除的字符串’)

select rtrim('**han teng fei**','*') from dual; substr(string,start,count)

取子字符串，从 start 开始，取 count 个

select substr('13088888888',3,8) from dual; replace(‘string’,‘s1’,‘s2’)

string 希望被替换的字符或变量

s1 被替换的字符串

s2 要替换的字符串

select replace('he love you','he','i') from dual; trim(‘s’ from ‘string’)

如果不指定参数，默认为空格符。

select trim(0 from 0009872348900) "trim example" from dual; 数学函数 ceil（向上取整）

返回大于或等于给出数字的最小整数；

select ceil(3.14159265) from dual; floor（向下取整）

对给定的数字取整数；

select floor(2345.67) from dual; trunc(精度截取)

按照指定的精度截取一个数；

select trunc(124.1666,-2),trunc(124.16666,2) from dual; round 和 trunk

按照指定的精度进行舍入；

round 函数为四舍五入

trunc（直接截取）

select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual; abs

返回指定值的绝对值；

select abs(100),abs(-100) from dual; acos

给出反余弦的值；

select acos(-1) from dual; asin

给出反正弦的值；

select asin(0.5) from dual; atan

返回一个数字的反正切值；

select atan(1) from dual; cos

返回一个给定数字的余弦；

select cos(-3.14159265) from dual; mod(n1,n2)

返回一个 n1 除以 n2 的余数；(取模函数)

select mod(10,3),mod(3,3),mod(2,3) from dual; 日期函数

介绍 :日期函数用于处理 date 类型的数据。

默认情况下日期格式是 dd-mon-yy 即 12-7 月-78

add_months(掌握)

add_months(日期值,增加(减少)值)增加或减去月份；

select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual; last_day

返回日期当月的最后一天；

select to_char(last_day(sysdate),'yyyy-mm-dd') from dual; months_between(date2,date1)

给出 date2-date1 的月份，共有多少个月；

select months_between('19-12 月-1999','19-3 月-1999') mon_between from dual; next_day(date,‘day’)

给出日期 date 和星期 X（day）之后计算下一个星期的日期；

select next_day('1-3 月-2017','星期五') next_day from dual; Sysdate

用来得到系统的当前日期；

select to_char(sysdate,'day') from dual; 转换类型函数 to_char(date,‘format’)

日期类型转换成字符串格式(主要用于将日期以习惯的格式输出显示)

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

to_char()

特别说明：日期格式：

yy:两位数字的年份 2004–04

yyyy:四位数字的年份 2004 年

mm:两位数字的月份 8 月–08

dd:两位数字的天数 30 号–30

hh24:二十四小时制 8 点–20

hh12:十二小时制 8 点–08 mi,ss–显示分钟\秒

day 显示星期几

month 显示几月

year 显示年

数字格式：

9:显示数字，并忽略前面 0

0:显示数字，如位数不足，则用 0 补齐

.:在指定位置显示小数点

,:在指定位置显示逗号

$:在数字前加美元符号

L:在数字前加本地货币符号

C:在数字前加国际货币符号

G:在指定位置显示组分隔符 D:在指定位置显示小数点符号(.)

说明：,逗号.和小数点可以合在一起使用，G 分隔符和 D 小数点符可以合在一起使用，但,.不能和 GD

综合使用，否则报错。

to_date(string,‘format’)

将字符串转换成日期(主要用于将日期按习惯的格式输入到 oracle 数据库中)

to_number

将给出的数字类型的字符转换为数字；

系统函数

decode 函数类似于 java 的 switch case 分支语句

decode (value, if1, then1, if2,then2, if3,then3, . . . else )

DECODE(VALUE,‘0’,‘1’,‘2’) 若value为0，则为1，否则为2

表之间的连接自连接

指在同一张表的连接查询(把一张表看作两张表)

显示员工的上级领导的姓名

select e2.ename from emp e1,emp e2 where e1.mgr=e2.empno; 内连接(inner join - on)

内连接基本语法：

select 列名 1,.. from 表 1 inner join 表 2 on 条件;

说明：内连接只有两张表同时满足条件才会被查询到。

显示员工的信息和部门名称

select e.*,d.dname from emp e,dept d where e.deptno=d.deptno;

等价于

select e.*,d.dname from emp e inner join dept d on e.deptno=d.deptno; 左外连接 (left join - on)

如果左侧的表完全显示我们就说是左外连接

基本语法：

select 列名 1,列名 2,.. from 表 1 left join 表 2 on 条件; select 列名 1,列名 2,.. from 表 1,表 2 where 条件 1=条件 2(+); 右外连接 (right join - on)

如果右侧的表完全显示我们就说是右外连接

基本语法：

select 列名 1,列名 2,.. from 表 1 right join 表 2 on 条件; select 列名 1,列名 2,.. from 表 1,表 2 where 条件 1(+)=条件 2; 完全外连 (full outer join - on)

完全显示两个表，没有匹配的记录置为空

基本语法：

select 列名 1,列 2,.. from 表 1 full outer join 表 2 on 条件; 分页查询 select t2.* from ( select t1.*, rownum rn from (select * from 表名) t1

where rownum=小范围(从第几条数据开始取);

特别说明：

oracle 分页查询是通过三层筛选法进行查询的。每一次都可以带 where 条件来对要查询的信息进行筛选。

第一层：构建我们所要查询字段信息并排序；

第二层：构建 rownum 别名 rn

第三层：加 where 条件，rn>=M and rn

【本文地址】

公司简介

联系我们